<?php
class M_CruiseItineraryTour extends Apps_Db_Table_Abstract {
    protected $_name = 'cruise_itinerary_tour';
    
    const STATUS_ACTIVE  	= 1;
    const STATUS_INACTIVE  	= 0;
    const STATUS_DELETED  	= -1;
    
    // get list cruise tour by bestSelling or promotion or featured
    public function listCruiseTourByType($categoryId = null, $type = 'best_selling', $limit = null) {
    	$q = $this->_db
                  ->select()
				  ->from(array('c_i_t'   => $this->_name), null)
				  ->join(array('c' => 'cruise'), 'c.id = c_i_t.cruise_id', null)
				  ->join(array('c_c' => 'cruise_category'), 'c_c.id = c.cruise_category_id', null)
				  ->join(array('a' 	 => 'article'), 'a.reference_id = c_i_t.id', null)
				  ->where('a.reference_type =?', M_DbTable_Article::REFERENCE_TYPE_CRUISE)
				  ->where('c_i_t.status =?', self::STATUS_ACTIVE)
				  ->order('c_i_t.created_date DESC')
				  ->columns(
				  		array('id'							=>  'c_i_t.id',
				  			  'thumbnail'					=>  'a.thumbnail'
				  ));

    	if($limit) {
    		$q->limit($limit);
    	}
    	if($categoryId) {
    		$q->where($categoryId);
    	}
    	
    	switch ($type) {
    		case 'best_selling':
                    $q->where('c_i_t.best_selling = ?', 1);
    		break;
    		case 'special_offer':
                    $q->where('c_i_t.special_offer = ?', 1);
    		break;
    		case 'featured':
                    $q->where('c_i_t.featured = ?', 1);
    		break;
    	}
                    
        if($this->getLang() == 'en') {
            $q->columns(array('name'        => 'c.en_name',
            				  'title'		=> 'a.en_title',
            				  'category_title'		=> 'c_c.en_title',
            				  'overview'	=> 'a.en_overview'));
        } else {
        	$q->columns(array('name'      	=> 'c.name',
            				  'title'		=> 'a.title',
            				  'category_title'		=> 'c_c.title',
            				  'overview'	=> 'a.overview'));
        }

        return $q;
    }
    
    //GET CRUISE BY CRUISE_ITINERARY_TOUR_ID
    public function getCruiseByCruiseItineraryTourId($cruiseItineraryTourId) {
    	$q = $this->_db
                  ->select()
				  ->from(array('c_i_t'   => $this->_name), null)
				  ->join(array('c' => 'cruise'), 'c.id = c_i_t.cruise_id', null)
				  ->where('c_i_t.id =?', $cruiseItineraryTourId)
				  ->columns(
				  		array('id'							=>  'c_i_t.id',
				  			  'cruise_id'					=>  'c.id'
				  ));
                    
        if($this->getLang() == 'en') {
            $q->columns(array('name'        => 'c.en_name'));
        } else {
        	$q->columns(array('name'      	=> 'c.name'));
        }
        return $q;
    }
    
    //GET CRUISE_ITINERARY_TOUR_ID BY CRUISE ID 
    public function getCruiseItineraryTourByCruiseId($cruiseId) {
    	$q = $this->_db
                  ->select()
				  ->from(array('c_i_t'   => $this->_name), null)
				  ->join(array('c' => 'cruise'), 'c.id = c_i_t.cruise_id', null)
				  ->join(array('c_i' => 'cruise_itinerary'), 'c_i.id = c_i_t.cruise_itinerary_id', null)
				  ->join(array('a' 	 => 'article'), 'a.reference_id = c_i_t.id', null)
				  ->where('a.reference_type =?', M_DbTable_Article::REFERENCE_TYPE_CRUISE)
				  ->where('c.id =?', $cruiseId)
				  ->order('c_i_t.sorting_number')
				  ->limit(1)
				  ->columns(
				  		array('id' =>  'c_i_t.id')
				  );
                    
        if($this->getLang() == 'en') {
            $q->columns(array('name'        => 'c.en_name', 
            				  'title'		=> 'a.en_title'));
        } else {
        	$q->columns(array('name'      	=> 'c.name', 
        					  'title'		=> 'a.title'));
        }
        return $q;
    }
    
    
    // LIST other cruise tour by crusie id
    public function listOtherCruiseTourByCruiseId($cruiseId, $cruiseItineraryTourId) {
    	$q = $this->_db
                  ->select()
				  ->from(array('c_i_t'   => $this->_name), null)
				  ->join(array('c' => 'cruise'), 'c.id = c_i_t.cruise_id', null)
				  ->join(array('a' => 'article'), 'a.reference_id = c_i_t.id', null)
				  ->where('a.reference_type =?', M_DbTable_Article::REFERENCE_TYPE_CRUISE)
				  ->where('c_i_t.status =?', self::STATUS_ACTIVE)
				  ->where('c_i_t.cruise_id =?', $cruiseId)
				  ->where('c_i_t.id !=?', $cruiseItineraryTourId)
				  ->order('c_i_t.created_date DESC')
				  ->columns(
				  		array('id' =>  'c_i_t.id'
				  ));
                    
        if($this->getLang() == 'en') {
            $q->columns(array('name'        => 'c.en_name',
            				  'title'		=> 'a.en_title'));
        } else {
        	$q->columns(array('name'      	=> 'c.name',
            				  'title'		=> 'a.title'));
        }
        return $q;
    }

    public function getCruiseItineraryTourInfo($cruiseItineraryTourId) {
    	$q = $this->_db
			   	  ->select()
				  ->from(array('c_i_t'   => $this->_name), null)
				  ->join(array('c' => 'cruise'), 'c.id = c_i_t.cruise_id', null)
				  ->join(array('c_c' => 'cruise_category'), 'c_c.id = c.cruise_category_id', null)
				  //->join(array('c_i' => 'cruise_itinerary'), 'c_i.id = c_i_t.cruise_itinerary_id', null)
				  ->join(array('a' 	 => 'article'), 'a.reference_id = c_i_t.id', null)
				  ->where('a.reference_type =?', M_DbTable_Article::REFERENCE_TYPE_CRUISE)
				  ->where('c_i_t.status =?', self::STATUS_ACTIVE)
				  ->where('c_i_t.id =?', $cruiseItineraryTourId)
			      ->columns(array('id' 			      => 'c_i_t.id',
        			    	      'meta_keyword'      => 'c_i_t.meta_keyword',
        			    	      'meta_description'  => 'c_i_t.meta_description',
			    				  'header_title'	  => 'c_i_t.header_title',
					    		  'detail_thumbnail'  => 'c_i_t.detail_thumbnail',
					    		  'category_id'	      => 'c_c.id',
					    		  'thumbnail'		  => 'a.thumbnail'));

    	if($this->getLang() == 'en') {
    		$q->columns(array('name'        => 'c.en_name',
		    				  'note_price'	=> 'c_i_t.en_note_price',
		    				  'title'		=> 'a.en_title',
		    				  'overview'	=> 'a.en_overview',
		    				  'detail'		=> 'a.en_detail',
		    				  'cruise_category_title' => 'c_c.en_title'));
    	} else {
    		$q->columns(array('name'      	=> 'c.name',
		    				  'note_price'	=> 'c_i_t.note_price',
		    				  'title'		=> 'a.title',
		    				  'overview'	=> 'a.overview',
		    				  'detail'		=> 'a.detail',
		    				  'cruise_category_title' => 'c_c.title'));
    	}
    	return $q;
    }
    
    // LIST ACTIVE
	public function getListActive() {
		$q = $this->_db
				  ->select()
				  ->from(array('c'   => $this->_name), null)
				  ->where('c.status =? ', M_DbTable_Cruise::STATUS_ACTIVE)
				  ->order('c.created_date DESC')
				  ->columns(
				  		array('id'						=>  'c.id',
							  'name'					=>	'c.name',
				  ));
		return $q;
	}
}